package com.soft.dao;

import com.soft.entity.Commodity;
import com.soft.util.DBHelper;
import com.soft.util.PageBean;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//产品管理,图片上传
public class CommodityDAO {
    public Map<String, Object> getAll(PageBean pb) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Commodity> list = new ArrayList<>();


        int totalCount = getTotalCount();
        pb.setTotalCount(totalCount);


        if (pb.getCurrentPage() <= 0) {
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());
        }


        int currentPage = pb.getCurrentPage();
        int index = (currentPage - 1) * pb.getPageCount();
        int count = pb.getPageCount();

        try {
            String sql = "select * from commodity limit ?,?;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setInt(1, index);
            stmt.setInt(2, count);
            rs = stmt.executeQuery();

            while (rs.next()) {
                Commodity lists = new Commodity();
                lists.setId(rs.getInt("id"));
                lists.setClass_id(rs.getInt("class_id"));
                lists.setTitle(rs.getString("title"));
                lists.setPrice(rs.getFloat("price"));
                lists.setSpecifications(rs.getString("specifications"));
                lists.setPlace(rs.getString("place"));
                lists.setAlcohol(rs.getString("alcohol"));
                lists.setPhoto(rs.getString("photo"));
                lists.setContent(rs.getString("content"));
                list.add(lists);
            }

            Map<String, Object> map = new HashMap<>();
            map.put("PageDate", pb);
            map.put("list", list);
            return map;
        }
        catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }



    public int getTotalCount() throws Exception {
        PreparedStatement pStatement = null;
        ResultSet rSet = null;
        String sql = "select count(*) from commodity";
        int count = 0;
        try {
            pStatement = DBHelper.getConnection().prepareStatement(sql);
            rSet = pStatement.executeQuery();
            rSet.next();
            count = rSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (rSet != null) {
                try {
                    rSet.close();
                    rSet = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (pStatement != null) {
                try {
                    pStatement.close();
                    pStatement = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return count;
    }



    public void add(Commodity commodity) {
        String sql = "insert into commodity(class_id,title,price,specifications,place,alcohol,photo,content) values(?,?,?,?,?,?,?,?);";
        PreparedStatement pstmt = null;

        try {
            pstmt = DBHelper.getConnection().prepareStatement(sql);
            pstmt.setInt(1, commodity.getClass_id());
            pstmt.setString(2, commodity.getTitle());
            pstmt.setFloat(3, commodity.getPrice());
            pstmt.setString(4, commodity.getSpecifications());
            pstmt.setString(5, commodity.getPlace());
            pstmt.setString(6, commodity.getAlcohol());
            pstmt.setString(7, commodity.getPhoto());
            pstmt.setString(8, commodity.getContent());
            pstmt.execute();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {

            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



    public void delete(Commodity commodity) {
        String sql = "delete from commodity where id=?;";
        PreparedStatement pstmt = null;

        try {
            pstmt = DBHelper.getConnection().prepareStatement(sql);
            pstmt.setInt(1, commodity.getId());
            pstmt.execute();
        }
        catch (Exception e) {
            throw new RuntimeException(e);
        } finally {

            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }




    public Commodity getone(Commodity commodity) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            String sql = "select * from commodity where id=? ;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setInt(1, commodity.getId());
            rs = stmt.executeQuery();

            if (rs.next()) {
                Commodity lists = new Commodity();
                lists.setId(rs.getInt("id"));
                lists.setClass_id(rs.getInt("class_id"));
                lists.setTitle(rs.getString("title"));
                lists.setPrice(rs.getFloat("price"));
                lists.setSpecifications(rs.getString("specifications"));
                lists.setPlace(rs.getString("place"));
                lists.setAlcohol(rs.getString("alcohol"));
                lists.setPhoto(rs.getString("photo"));
                lists.setContent(rs.getString("content"));
                return lists;
            }
            return null;
        }
        catch (Exception ex) {
            ex.printStackTrace();
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return null;
    }




    public Map<String, Object> search(PageBean pb, Commodity commodity) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Commodity> list = new ArrayList<>();


        int totalCount = getTotalCount();
        pb.setTotalCount(totalCount);


        if (pb.getCurrentPage() <= 0) {
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());
        }

        int currentPage = pb.getCurrentPage();
        int index = (currentPage - 1) * pb.getPageCount();
        int count = pb.getPageCount();
        try {
            String sql = "select * from commodity where title like concat('%',?,'%') limit ?,? ;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setString(1, commodity.getTitle());
            stmt.setInt(2, index);
            stmt.setInt(3, count);
            rs = stmt.executeQuery();

            while (rs.next()) {
                Commodity lists = new Commodity();
                lists.setId(rs.getInt("id"));
                lists.setClass_id(rs.getInt("class_id"));
                lists.setTitle(rs.getString("title"));
                lists.setPrice(rs.getFloat("price"));
                lists.setSpecifications(rs.getString("specifications"));
                lists.setPlace(rs.getString("place"));
                lists.setAlcohol(rs.getString("alcohol"));
                lists.setPhoto(rs.getString("photo"));
                lists.setContent(rs.getString("content"));
                list.add(lists);
            }

            Map<String, Object> map = new HashMap<>();
            map.put("PageDate", pb);
            map.put("list", list);
            return map;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return null;
    }



    public void update(Commodity commodity) {
        PreparedStatement pstmt = null;
        try {
            String photo = commodity.getPhoto();
            if (photo == null || "".equals(photo.trim())) {
                String sql = "update commodity set class_id=?,title=?,price=?,specifications=?,place=?,alcohol=?,content=? where id=? ;";
                pstmt = DBHelper.getConnection().prepareStatement(sql);
                pstmt.setInt(1, commodity.getClass_id());
                pstmt.setString(2, commodity.getTitle());
                pstmt.setFloat(3, commodity.getPrice());
                pstmt.setString(4, commodity.getSpecifications());
                pstmt.setString(5, commodity.getPlace());
                pstmt.setString(6, commodity.getAlcohol());
                pstmt.setString(7, commodity.getContent());
                pstmt.setInt(8, commodity.getId());
                pstmt.execute();
            } else {
                String sql = "update commodity set class_id=?,title=?,price=?,specifications=?,place=?,alcohol=?,photo=?,content=? where id=? ;";
                pstmt = DBHelper.getConnection().prepareStatement(sql);
                pstmt.setInt(1, commodity.getClass_id());
                pstmt.setString(2, commodity.getTitle());
                pstmt.setFloat(3, commodity.getPrice());
                pstmt.setString(4, commodity.getSpecifications());
                pstmt.setString(5, commodity.getPlace());
                pstmt.setString(6, commodity.getAlcohol());
                pstmt.setString(7, photo);
                pstmt.setString(8, commodity.getContent());
                pstmt.setInt(9, commodity.getId());
                pstmt.execute();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {

            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
